package zdori;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class J03_MemberDAO implements J03_IMemberDAO{

	private String DRV="oracle.jdbc.driver.OracleDriver";
	//private String URL="jdbc:oracle:thin:@112.216.253.125:1521:xe?user=gdori&amp;password=gdori";
	private String URL="jdbc:oracle:thin:gdori/gdori@112.216.253.125:1521:xe";
	private String insertSQL = " insert into J03MEMBER values (exam.nextval,?,?,?,?,sysdate) ";
	private String selectSQL = " select * from J03MEMBER order by mem_no desc ";
	private String updateSQL = " update J03MEMBER set mem_pw = ?, mem_name = ?, mem_email = ? where mem_no = ? ";
	private String deleteSQL = " delete from J03MEMBER where mem_no = ? ";
	//private String USR="gdori";
	//private String PWD="gdori";
	
	@Override
	public boolean insertMember(J03_MemberVO m) {
		// TODO Auto-generated method stub
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try {
			//Class.forName(DRV);
			//conn = DriverManager.getConnection(URL);
			conn = this.openDataBase();
			pstmt = conn.prepareStatement(insertSQL);
			//? 매개변수에 대입할 값 지정
			pstmt.setString(1, m.getUserid());
			pstmt.setString(2, m.getPasswd());
			pstmt.setString(3, m.getName());
			pstmt.setString(4, m.getEmail());
			
			pstmt.executeUpdate();
			
			result = true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally{
			this.closeDatabase(conn, pstmt, null);
		}
		
		return result;
	}
	
	
	//select 문으로 전체 회원정보를 조회한 후 
	//MemberVO를 내용으로 갖는 동적배열 List 로 결과를 넘김
	@Override
	public List<J03_MemberVO> selectMember() {
		// TODO Auto-generated method stub
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<J03_MemberVO> result = new ArrayList<>();
		
		try {
			conn = openDataBase();
			pstmt = conn.prepareStatement(selectSQL);
			rs = pstmt.executeQuery();
			while(rs.next()){
				J03_MemberVO m = new J03_MemberVO();
				m.setMno(rs.getString("mem_no"));
				//System.out.println("mem_id : "+rs.getString("mem_id"));
				m.setUserid(rs.getString("mem_id"));
				m.setPasswd((rs.getString("mem_pw")));
				m.setName (rs.getString("mem_name"));
				m.setEmail(rs.getString("mem_email"));
				m.setRegdate(rs.getString("mem_reg_date"));
				
				result.add(m);
			}
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			this.closeDatabase(conn, pstmt, rs);
		}
		
		
		return result;
	}
	
	@Override
	public boolean updateMember(J03_MemberVO m) {
		// TODO Auto-generated method stub
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try {
			//Class.forName(DRV);
			//conn = DriverManager.getConnection(URL);
			conn = this.openDataBase();
			pstmt = conn.prepareStatement(updateSQL);
			//? 매개변수에 대입할 값 지정
			//pstmt.setString(1, m.getUserid());
			pstmt.setString(1, m.getPasswd());
			pstmt.setString(2, m.getName());
			pstmt.setString(3, m.getEmail());
			pstmt.setString(4, m.getMno());
			
			int cnt = pstmt.executeUpdate();
			if(cnt == 0){
				System.out.println("실행됨 그러나 수정된 데이터는 없음");
			}else{
				result = true;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally{
			this.closeDatabase(conn, pstmt, null);
		}
		
		return result;
	}
	
	@Override
	public boolean removeMember(J03_MemberVO m) {
		// TODO Auto-generated method stub
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try {
			//Class.forName(DRV);
			//conn = DriverManager.getConnection(URL);
			conn = this.openDataBase();
			pstmt = conn.prepareStatement(deleteSQL);
			//? 매개변수에 대입할 값 지정
			//pstmt.setString(1, m.getUserid());
			
			pstmt.setString(1, m.getMno());
			
			int cnt = pstmt.executeUpdate();
			if(cnt == 0){
				System.out.println("실행됨 그러나 삭제된 데이터는 없음");
			}else{
				result = true;
			}
			
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally{
			this.closeDatabase(conn, pstmt, null);
		}
		
		return result;
		
	}
	//jdbc connection 객체를 생성하는 메서드
	private Connection openDataBase(){
		
		try {
			Class.forName(DRV);
			return DriverManager.getConnection(URL);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		
		return null;
	}
		
	//jdbc 객체를 닫는 메서드
	private void closeDatabase(Connection conn, PreparedStatement pstmt, ResultSet rs){
		if(rs != null){
			try {
				rs.close();
				rs = null;
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		if(pstmt != null){
			try {
				pstmt.close();
				pstmt = null;
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		
		if(conn != null){
			try {
				conn.close();
				conn = null;
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
	}
	
	
}
